/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.fandi.dao.impl;

import com.fandi.config.DatabaseConnection;
import com.fandi.dao.ItemDao;
import com.fandi.model.ItemModel;
import com.fandi.util.DateFormatterUtil;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Fandy
 */
public class ItemDaoImpl implements ItemDao {

    private final Connection conn;
    
    private final String insert     = "INSERT INTO tb_item VALUES(null, ?, ?, ?, ?, ?, ?, now())";
    private final String update     = "UPDATE tb_item SET category_id=?, code=?, item_name=?, qty=?, expired=?, description=?, last_upd=now() WHERE uid=?";
    private final String delete     = "DELETE FROM tb_item WHERE code=?";
    private final String select     = "SELECT * FROM tb_item";
    private final String selectBy   = "SELECT * FROM tb_item WHERE code=?";
    private final String search     = "SELECT * FROM tb_item WHERE code LIKE '%' ? '%' ";
    private final String count      = "SELECT COUNT(*) as totalRecord FROM tb_item";
    private final String countBy    = "SELECT COUNT(*) as totalRecord FROM tb_item WHERE expired < ?";

    public ItemDaoImpl() {
        conn = DatabaseConnection.getConnection();
    }
    
    @Override
    public void insert(ItemModel model) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(insert)) {
            ps.setInt(1, model.getCatId());
            ps.setString(2, model.getCode());
            ps.setString(3, model.getName());
            ps.setInt(4, model.getQty());
            ps.setDate(5, new Date(model.getExpired().getTime()));
            ps.setString(6, model.getDescription());
            
            ps.executeUpdate();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    @Override
    public void update(ItemModel model) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(update)) {
            ps.setInt(1, model.getCatId());
            ps.setString(2, model.getCode());
            ps.setString(3, model.getName());
            ps.setInt(4, model.getQty());
            ps.setDate(5, new Date(model.getExpired().getTime()));
            ps.setString(6, model.getDescription());
            ps.setInt(7, model.getUid());
            
            ps.executeUpdate();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    @Override
    public boolean delete(String code) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(delete)) {
            ps.setString(1, code);
            
            ps.executeUpdate();
            
            return true;
        } catch (SQLException se) {
            se.printStackTrace();
            return false;
        }
    }

    @Override
    public List<ItemModel> selectAll() throws SQLException {
        List<ItemModel> list = new ArrayList<>();
        
        try (
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(select)
            ) {
            
            while (rs.next()) {
                ItemModel model = new ItemModel();
                model.setUid(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("item_name"));
                model.setQty(rs.getInt("qty"));
                model.setExpired(rs.getDate("expired"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));

                list.add(model);
            }

            return list;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public ItemModel selectBy(String code) throws SQLException {
        ItemModel model = null;
        
        try (
            PreparedStatement ps = conn.prepareStatement(selectBy);
            ) {
            
            ps.setString(1, code);
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                model = new ItemModel();
                model.setUid(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("item_name"));
                model.setQty(rs.getInt("qty"));
                model.setExpired(rs.getDate("expired"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));
            }

            return model;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public List<ItemModel> searchBy(String code) throws SQLException {
        List<ItemModel> list = new ArrayList<>();
        PreparedStatement ps = null;
        
        try /*(
                PreparedStatement ps = conn.prepareStatement(search);
            )*/ {
            ps = conn.prepareStatement(search);
            ps.setString(1, code);
            
            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                ItemModel model = new ItemModel();
                model.setUid(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("item_name"));
                model.setQty(rs.getInt("qty"));
                model.setExpired(rs.getDate("expired"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));

                list.add(model);
            }
            
            return list;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public int count(boolean isExpired) throws SQLException {
        PreparedStatement ps = null;
        int totalRows = 0;
        
        String today = DateFormatterUtil.formatNow("", new java.util.Date());
        
        try {
            
            ps = conn.prepareStatement(count);
            
            if (isExpired) {
                ps = conn.prepareStatement(countBy);
                ps.setString(1, today);
            }
            
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                totalRows = rs.getInt("totalRecord");
            }
            
            return totalRows;
        } catch (SQLException se) {
            se.printStackTrace();
            return 0;
        }
    }
    
}
